MBO: SQL优化之基于物化视图的优化

MBO是Materialized View Based Optimizer的缩写，译为基于物化视图的优化器。MBO的主要功能是对于给定的语句，基于已有的物化视图，生成最佳的执行计划，使得能够直接抽取物化视图的结果，再进行符合原业务逻辑的加工。

由于经过优化之后，MBO会将待访问的数据集缩小到物化视图，因此达到加快查询的优化效果。

目前Inceptor中的物化视图共有2种形式：

1. 原始的物化视图，即通过专门的DDL创建出来的物化视图；

2. 通过Transwarp Rubik创建的Cube实例，Inceptor需要与Studio交互获取Cube实例的表名以及对应的DDL。Cube的创建方法请参考OLAP Cube可视化设计工具—Transwarp Rubik。

与MBO相关的参数设置

MBO的使用涉及3个配置：

• inceptor.mbo.enable：MBO总开关，默认为false。

• inceptor.mbo.for.naive.mv：原始物化视图是否可以作为MBO的source，即是否将其作为预计算结果对执行语句进行优化，默认为false。

• inceptor.mbo.for.cube：Cube表是否作为MBO的source，即是否将Cube表作为预计算结果对执行语句进行优化，默认为false。注意，如果系统没有Studio环境，即使inceptor.mbo.for.cube为true，MBO也不会利用Cube表来优化执行计划。

MBO支持的场景

聚合

目前支持两类聚合的匹配：

1. GROUP BY程度一致：即语句和物化视图的聚合字段可以完全匹配。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv 

ENABLE REWRITE AS

SELECTc1, c2, SUM(v1) AS sv1, COUNT(v1)  AS cv1

FROM ttt GROUP BY c1,c2;

语句：

SELECT c1, c2, AVG(v1) FROM ttt GROUP BY c1, c2;

因为物化视图和语句的聚合列都是c1, c2，所以MBO只需要在执行计划中访问mv，并添加必要的SELECT部分。优化后的执行计划等价于：

SELECT c1, c2, sv1/cv1 FROM mv;

2. 额外一层聚合：物化视图的聚合粒度比语句的粒度更细，MBO会添加额外一层聚合来保证结果的一致性。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv 

ENABLE REWRITE AS

SELECT c1, c2,

SUM(v1) AS sv1,

COUNT(v1)  AS cv1,

MAX(v1) AS maxv1,

MIN(v1) AS minv1,

AVG(v1) AS avgv1

FROM ttt GROUP BY c1,c2;

语句：

SELECT c1, SUM(v1),

COUNT(v1),

MAX(v1),

MIN(v1),

AVG(v1)

FROM ttt GROUP BY c1;

因为物化视图对c1,c2都做了GROUP BY，而语句只对c1做GROUP BY，所以MBO需要在新的执行计划中添加GROUP BY c1，并且要对细粒度的计算结果进行一次汇总处理。优化后的执行计划等价于：

SELECT c1, SUM(sv1),

SUM(cv1),

MAX(maxv1),

MIN(minv1),

SUM(sv1)/SUM(cv1)

FROM mv GROUP BY c1;

过滤

当物化视图和语句都带过滤时，如果语句的过滤条件真包含于物化视图的过滤条件，则可以通过收缩过滤条件来改变执行计划。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv

ENABLE REWRITE AS

SELECT * FROM a WHERE c1 > 10;

语句：

SELECT * FROM a WHERE c1 > 15;

优化后的执行计划等价于：

SELECT * FROM mv WHERE c1 > 15;

关联

目前MBO优化支持完全匹配的JOIN以及INNER JOIN中JOIN的部分匹配。

1. 完全匹配

完全匹配是指满足下面三种条件的JOIN：

参与JOIN的表一致；JOIN的 类型一致；JOIN条件一致。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv

ENABLE REWRITE AS

SELECT a.v1, a.id2

FROM a JOIN b ON a.id = b.id

JOIN c ON a.id1 = c.id;

语句：

SELECT a.v1, a.id2

FRO M a JOIN b ON  a.id = b.id

JOIN c ON a.id1 = c.id;

优化后的执行计划等价于：

SELECT v1, id2 FROM mv;

2. INNER JOIN中JOIN的部分匹配

如果物化视图的JOIN是语句JOIN中的一部分（注意只有满足这种从属顺序的关系才可以，相反则无法优化），且同为INNER JOIN，那么MBO就可以进行优化。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv

ENABLE REWRITE AS

SELECT a.v1, a.id2

FROM a JOIN b ON a.id = b.id

JOIN c ON a.id1 = c.id;

语句：

SELECT a.v1 FROM a

JOIN c ON a.id1 = c.id

JOIN b ON a.id = b.id

JOIN d ON a.id2 = d.id;

由于物化视图的INNER JOIN是语句中一部分，所以优化后的执行计划等价于：

SELECT mv.v1 FROM mv

JOIN d ON mv.id2 = d.id;

部分重写

部分重写指的是，如果语句中的部分能匹配物化视图，那么MBO就可以对执行计划中的一部分进行重写。

【例如】物化视图：

CREATE MATERIALIZED VIEW mv

ENABLE REWRITE AS

SELECT a.v1 FROM a JOIN b ON a.id = b.id;

语句：

SELECT a.v1 FROM a JOIN b ON a.id = b.id

UNION

SELECT a.v2 FROM a JOIN b ON a.id = b.id;

优化后的执行计划等价于：

SELECT mv.v1 FROM mv

UNION

SELECT a.v2 FROM a JOIN b ON a.id = b.id;

运用Rubik与MBO优化OLAP业务

如同OLAP Cube可视化设计工具—Transwarp Rubik介绍的，对于OLAP业务，通常我们会通过Rubik，提前建立Cube并进行实例化，获得按照业务聚合的计算结果，并以表的形式存储在Inceptor中。但是完成Cube的创建只是走完了OLAP业务优化道路的一半，还必须将这些预计算结果运用于优化执行，这时需要依靠MBO，自动选出合适的目标Cube表，生成最佳执行计划。

下面我们以一则实例说明MBO的优化效果。

假设当前需要执行下面这条语句：

SELECT p_type, p_brand, p_size, AVG(ps_supplycost)

FROM part p, partsupp ps

WHERE p.p_partkey = ps.partkey

GROUP BY p_type, p_brand, p_size;

其中partsupp是事实表，part为维度表。为了加快执行，我们在Rubik中建立Cube。首先创建维度part_dimension，它包含一个单一级别，该级别的属性有partsupp.p_brand、partsupp.p_size、partsupp.p_type，以保证这三个字段总是作为聚合字段同时出现。接着将part_dimension通过条件part.p_partkey = partsupp.partkey同事实表part进行关联，并在实例化的时候，选择对AVG(ps_supplycost)进行预先计算。

在执行上述语句之前开启MBO开关，并确保允许MBO将Cube表作为source。经对比，开启MBO后该语句的执行速度与未开MBO时相比得到了很大的提升。

上述过程重现于下面的视频中。

https://v.qq.com/txp/iframe/player.html?vid=x1321b54vwu&width=500&height=375&auto=0

总结

物化视图的好处是能够提前聚合运算和关联运算，使之后的语句可以直接利用其结果，避免在系统中反复执行这些计算量大的操作。但是，当系统中存在很多物化视图时，用户往往难以根据执行语句从中做出合理选择，进行语句优化。而MBO恰好能够解决此问题，进行自动化的择取和执行计划优化，充分发挥物化视图本身具备的优化特性，从而提升分析系统的可用性。

---

往期原创文章

大数据开放实验室由星环信息科技（上海）有限公司运营，专门致力于大数据技术的研究和传播。若转载请在文章开头明显注明“文章来源于微信订阅号——大数据开放实验室”，并保留作者和账号介绍。